package com.skyeye;

import com.skyeye.entity.User;
import com.skyeye.util.DatabaseUtil;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

import static org.junit.Assert.*;

/**
 * 用户CRUD操作单元测试类
 * 通过.properties配置文件配置数据库连接
 * 测试用户表的增删改查操作
 */
public class UserCRUDTest {
    
    private static final String TABLE_NAME = DatabaseUtil.getTableName();
    
    @Before
    public void setUp() {
        // 测试前创建测试表
//        DatabaseUtil.createTestTable();
//        System.out.println("🚀 开始执行CRUD测试...");
    }
    
    @After
    public void tearDown() {
        // 测试后清空测试表数据（保留表结构）
//        DatabaseUtil.truncateTestTable();
//        System.out.println("🧹 测试完成，清理测试数据");
    }
    
    /**
     * 测试插入用户（Create）
     */
    @Test
    public void testInsertUser() {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        
        try {
            connection = DatabaseUtil.getConnection();
            
            // 准备测试数据
            User testUser = new User("张三", "zhangsan@example.com", 25, "13800138000");
            
            // 插入SQL
            String insertSQL = "INSERT INTO " + TABLE_NAME + 
                    " (username, email, age, phone, create_time, update_time) VALUES (?, ?, ?, ?, ?, ?)";
            
            statement = connection.prepareStatement(insertSQL, Statement.RETURN_GENERATED_KEYS);
            statement.setString(1, testUser.getUsername());
            statement.setString(2, testUser.getEmail());
            statement.setInt(3, testUser.getAge());
            statement.setString(4, testUser.getPhone());
            statement.setTimestamp(5, Timestamp.valueOf(testUser.getCreateTime()));
            statement.setTimestamp(6, Timestamp.valueOf(testUser.getUpdateTime()));
            
            // 执行插入
            int affectedRows = statement.executeUpdate();
            assertEquals("应该插入1条记录", 1, affectedRows);
            
            // 获取生成的主键
            resultSet = statement.getGeneratedKeys();
            assertTrue("应该生成主键", resultSet.next());
            int generatedId = resultSet.getInt(1);
            testUser.setId(generatedId);
            
            System.out.println("✅ 用户插入成功！ID: " + generatedId);
            System.out.println("插入的用户信息: " + testUser);
            
        } catch (SQLException e) {
            fail("插入用户失败: " + e.getMessage());
        } finally {
            DatabaseUtil.closeAll(connection, statement, resultSet);
        }
    }
    
    /**
     * 测试查询用户（Read）
     */
    @Test
    public void testSelectUser() {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        
        try {
            connection = DatabaseUtil.getConnection();
            
            // 先插入测试数据
            insertTestUser(connection, "李四", "lisi@example.com", 30, "13900139000");
            
            // 查询SQL
            String selectSQL = "SELECT * FROM " + TABLE_NAME + " WHERE username = ?";
            statement = connection.prepareStatement(selectSQL);
            statement.setString(1, "李四");
            
            resultSet = statement.executeQuery();
            
            // 验证查询结果
            assertTrue("应该查询到用户", resultSet.next());
            
            User user = new User();
            user.setId(resultSet.getInt("id"));
            user.setUsername(resultSet.getString("username"));
            user.setEmail(resultSet.getString("email"));
            user.setAge(resultSet.getInt("age"));
            user.setPhone(resultSet.getString("phone"));
            user.setCreateTime(resultSet.getTimestamp("create_time").toLocalDateTime());
            user.setUpdateTime(resultSet.getTimestamp("update_time").toLocalDateTime());
            
            assertEquals("用户名应该匹配", "李四", user.getUsername());
            assertEquals("邮箱应该匹配", "lisi@example.com", user.getEmail());
            assertEquals("年龄应该匹配", Integer.valueOf(30), user.getAge());
            assertEquals("电话应该匹配", "13900139000", user.getPhone());
            
            System.out.println("✅ 用户查询成功！");
            System.out.println("查询到的用户信息: " + user);
            
        } catch (SQLException e) {
            fail("查询用户失败: " + e.getMessage());
        } finally {
            DatabaseUtil.closeAll(connection, statement, resultSet);
        }
    }
    
    /**
     * 测试更新用户（Update）
     */
    @Test
    public void testUpdateUser() {
        Connection connection = null;
        PreparedStatement statement = null;
        
        try {
            connection = DatabaseUtil.getConnection();
            
            // 先插入测试数据
            int userId = insertTestUser(connection, "王五", "wangwu@example.com", 28, "13700137000");
            
            // 更新SQL
            String updateSQL = "UPDATE " + TABLE_NAME + 
                    " SET email = ?, age = ?, phone = ?, update_time = ? WHERE id = ?";
            
            statement = connection.prepareStatement(updateSQL);
            statement.setString(1, "wangwu_new@example.com");
            statement.setInt(2, 29);
            statement.setString(3, "13700137001");
            statement.setTimestamp(4, Timestamp.valueOf(LocalDateTime.now()));
            statement.setInt(5, userId);
            
            // 执行更新
            int affectedRows = statement.executeUpdate();
            assertEquals("应该更新1条记录", 1, affectedRows);
            
            System.out.println("✅ 用户更新成功！用户ID: " + userId);
            
            // 验证更新结果
            statement.close();
            String selectSQL = "SELECT * FROM " + TABLE_NAME + " WHERE id = ?";
            statement = connection.prepareStatement(selectSQL);
            statement.setInt(1, userId);
            
            ResultSet resultSet = statement.executeQuery();
            assertTrue("应该查询到更新后的用户", resultSet.next());
            assertEquals("邮箱应该已更新", "wangwu_new@example.com", resultSet.getString("email"));
            assertEquals("年龄应该已更新", 29, resultSet.getInt("age"));
            assertEquals("电话应该已更新", "13700137001", resultSet.getString("phone"));
            
        } catch (SQLException e) {
            fail("更新用户失败: " + e.getMessage());
        } finally {
            DatabaseUtil.closeAll(connection, statement, null);
        }
    }
    
    /**
     * 测试删除用户（Delete）
     */
    @Test
    public void testDeleteUser() {
        Connection connection = null;
        PreparedStatement statement = null;
        
        try {
            connection = DatabaseUtil.getConnection();
            
            // 先插入测试数据
            int userId = insertTestUser(connection, "赵六", "zhaoliu@example.com", 35, "13600136000");
            
            // 删除SQL
            String deleteSQL = "DELETE FROM " + TABLE_NAME + " WHERE id = ?";
            statement = connection.prepareStatement(deleteSQL);
            statement.setInt(1, userId);
            
            // 执行删除
            int affectedRows = statement.executeUpdate();
            assertEquals("应该删除1条记录", 1, affectedRows);
            
            System.out.println("✅ 用户删除成功！用户ID: " + userId);
            
            // 验证删除结果
            statement.close();
            String selectSQL = "SELECT COUNT(*) FROM " + TABLE_NAME + " WHERE id = ?";
            statement = connection.prepareStatement(selectSQL);
            statement.setInt(1, userId);
            
            ResultSet resultSet = statement.executeQuery();
            assertTrue("应该查询到结果", resultSet.next());
            assertEquals("用户应该已被删除", 0, resultSet.getInt(1));
            
        } catch (SQLException e) {
            fail("删除用户失败: " + e.getMessage());
        } finally {
            DatabaseUtil.closeAll(connection, statement, null);
        }
    }
    
    /**
     * 测试查询所有用户
     */
    @Test
    public void testSelectAllUsers() {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        
        try {
            connection = DatabaseUtil.getConnection();
            
            // 插入多条测试数据
            insertTestUser(connection, "用户1", "user1@example.com", 20, "13100131000");
            insertTestUser(connection, "用户2", "user2@example.com", 25, "13200132000");
            insertTestUser(connection, "用户3", "user3@example.com", 30, "13300133000");
            
            // 查询所有用户
            String selectAllSQL = "SELECT * FROM " + TABLE_NAME + " ORDER BY id";
            statement = connection.prepareStatement(selectAllSQL);
            resultSet = statement.executeQuery();
            
            // 收集查询结果
            List<User> users = new ArrayList<>();
            while (resultSet.next()) {
                User user = new User();
                user.setId(resultSet.getInt("id"));
                user.setUsername(resultSet.getString("username"));
                user.setEmail(resultSet.getString("email"));
                user.setAge(resultSet.getInt("age"));
                user.setPhone(resultSet.getString("phone"));
                user.setCreateTime(resultSet.getTimestamp("create_time").toLocalDateTime());
                user.setUpdateTime(resultSet.getTimestamp("update_time").toLocalDateTime());
                users.add(user);
            }
            
            // 验证查询结果
            assertEquals("应该查询到3个用户", 3, users.size());
            
            System.out.println("✅ 查询所有用户成功！");
            System.out.println("查询到的用户数量: " + users.size());
            for (User user : users) {
                System.out.println("用户信息: " + user);
            }
            
        } catch (SQLException e) {
            fail("查询所有用户失败: " + e.getMessage());
        } finally {
            DatabaseUtil.closeAll(connection, statement, resultSet);
        }
    }
    
    /**
     * 测试根据条件查询用户
     */
    @Test
    public void testSelectUsersByCondition() {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        
        try {
            connection = DatabaseUtil.getConnection();
            
            // 插入测试数据
            insertTestUser(connection, "小明", "xiaoming@example.com", 22, "13400134000");
            insertTestUser(connection, "小红", "xiaohong@example.com", 23, "13500135000");
            insertTestUser(connection, "小刚", "xiaogang@example.com", 24, "13600136000");
            
            // 根据年龄范围查询
            String selectByAgeSQL = "SELECT * FROM " + TABLE_NAME + " WHERE age >= ? AND age <= ? ORDER BY age";
            statement = connection.prepareStatement(selectByAgeSQL);
            statement.setInt(1, 22);
            statement.setInt(2, 23);
            
            resultSet = statement.executeQuery();
            
            // 收集查询结果
            List<User> users = new ArrayList<>();
            while (resultSet.next()) {
                User user = new User();
                user.setId(resultSet.getInt("id"));
                user.setUsername(resultSet.getString("username"));
                user.setEmail(resultSet.getString("email"));
                user.setAge(resultSet.getInt("age"));
                user.setPhone(resultSet.getString("phone"));
                users.add(user);
            }
            
            // 验证查询结果
            assertEquals("应该查询到2个用户（年龄22-23）", 2, users.size());
            
            System.out.println("✅ 根据条件查询用户成功！");
            System.out.println("查询到的用户数量: " + users.size());
            for (User user : users) {
                System.out.println("用户信息: " + user);
            }
            
        } catch (SQLException e) {
            fail("根据条件查询用户失败: " + e.getMessage());
        } finally {
            DatabaseUtil.closeAll(connection, statement, resultSet);
        }
    }
    
    /**
     * 辅助方法：插入测试用户并返回用户ID
     */
    private int insertTestUser(Connection connection, String username, String email, int age, String phone) 
            throws SQLException {
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        
        try {
            String insertSQL = "INSERT INTO " + TABLE_NAME + 
                    " (username, email, age, phone, create_time, update_time) VALUES (?, ?, ?, ?, ?, ?)";
            
            statement = connection.prepareStatement(insertSQL, Statement.RETURN_GENERATED_KEYS);
            statement.setString(1, username);
            statement.setString(2, email);
            statement.setInt(3, age);
            statement.setString(4, phone);
            statement.setTimestamp(5, Timestamp.valueOf(LocalDateTime.now()));
            statement.setTimestamp(6, Timestamp.valueOf(LocalDateTime.now()));
            
            statement.executeUpdate();
            
            resultSet = statement.getGeneratedKeys();
            resultSet.next();
            return resultSet.getInt(1);
            
        } finally {
            DatabaseUtil.closeAll(null, statement, resultSet);
        }
    }
}
